package com.aierkeji.call.cms.dao.jpa;

import java.util.Date;

import javax.transaction.Transactional;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.aierkeji.call.cms.dao.BaseJPADao;
import com.aierkeji.call.cms.dao.jpa.custom.AkjCallClueDaoCustom;
import com.aierkeji.call.cms.entity.AkjCallClue;
import com.aierkeji.call.cms.entity.AkjCallClueGroup;
import com.aierkeji.call.cms.entity.snap.AkjCallUserSnap;

public interface AkjCallClueDao extends BaseJPADao<AkjCallClue, Long>, AkjCallClueDaoCustom {

	int countByClueGroup(@Param("ClueGroup") AkjCallClueGroup clueGroup);

	@Query("delete from AkjCallClue where id in (:ids) and createdBy.id = :#{#createdBy.id}  ")
	@Modifying
	int deleteClueByIds(@Param("ids") Long[] ids, AkjCallUserSnap createdBy);

	@Query(value = " delete akj_call_clue from akj_call_clue ,(select phone,"
			+ " min(id) id2 from akj_call_clue where clue_group_id = :groupId and phone is not null group by phone having count(id) > 1)t2"
			+ "  where  akj_call_clue.phone = t2.phone and akj_call_clue.clue_group_id = :groupId  and akj_call_clue.id != t2.id2 	"
//			+ " and akj_call_clue.assigned_at is null "
			, nativeQuery = true)
	@Modifying
	int deleteDuplicate(@Param("groupId") Long groupId);

	@Query("delete from AkjCallClue where clueGroup.id = :#{#akjCallClueGroup.id}")
	@Modifying
	int deleteByClueGroup(AkjCallClueGroup akjCallClueGroup);

	@Query("select count(1) from AkjCallClue where createdBy=:createdBy and clueGroup=:clueGroup and assignedAt is null")
	int countNoAssigned(@Param("createdBy") AkjCallUserSnap akjCallUserSnap,
			@Param("clueGroup") AkjCallClueGroup clueGroup);

	@Query(value = "select id from AkjCallClue where createdBy=:createdBy and clueGroup=:clueGroup and assignedAt is null order by createdAt desc,id desc ", countQuery = "select count(1)  from AkjCallClue where createdBy=:createdBy and clueGroup=:clueGroup and assignedAt is null")
	Page<Long> getNoAssigendId(@Param("createdBy") AkjCallUserSnap akjCallUserSnap,
			@Param("clueGroup") AkjCallClueGroup clueGroup, Pageable pageable);

	@Query("update AkjCallClue set assignedAt = null,assignedTo=null,backCount=backCount+1,lastCallStatus=null,recycledAt=:recycledAt where assignedAt is not null and lastCallAt is null and assignedAt<:assignedAt and createdBy.id = :createdBy ")
	@Modifying
	int assigneRecycle(@Param("recycledAt")Date recycledAt,@Param("assignedAt") Date assignedAt,@Param("createdBy") Long createdBy);

	@Query(value = "delete from akj_call_clue where exists ( select 1 from (select clue.id id from akj_call_clue clue left join akj_call_clue_group cluegroup on clue.clue_group_id = cluegroup.id where cluegroup.is_delete = 0"
			+ " and  clue.created_by = :createdBy 	and clue.created_at <= :endDate and clue.created_at >=:startDate order by clue.created_at desc limit :pageNo ,:pageSize ) a1 where a1.id = akj_call_clue.id ) ", nativeQuery = true)
	@Modifying
	@Transactional
	int deleteClueByBackups(@Param("startDate") Date startDate, @Param("endDate") Date endDate,
			@Param("createdBy") Long createdBy, @Param("pageNo") int pageNo, @Param("pageSize") int pageSize);
}
